#!/usr/bin/env python
# coding=UTF-8
'''
@Author: 赵天天
@LastEditors: 赵天天
@email: dayday_1996@163.com
@Descripttion: 任务管理查看任务接口
@Date: 2019-04-28 17:28:28
@LastEditTime: 2019-04-29 16:40:11
'''

from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
import json
from config import config
from lib import auth
from lib.table import table
from lib.validform import V, ValidateForm

app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 配置基本项数据
# 获得节点信息
@app.route('/api/task/getallnode', methods=['POST'])
@auth.permission("task")
def getallnode():
    try:
        g.cursor.execute("""select distinct sys_node_id,
                                sys_node_name
                            from (
                                select a.agency_id ,
                                a.agency_name ,
                                c.sys_node_id ,
                                c.sys_node_name
                                from sys_agency_info as a
                                left join h_information_system as b 
                                on a.agency_id = b.agency_id
                                left join h_system_node as c 
                                on b.info_sys_id = c.info_sys_id and  
                                a.state = '1' and  
                                b.state = '1' and  
                                c.state = '1' ) as a 
                            where a.sys_node_id <> '' and  sys_node_name <> ''
                            """)
        allnode = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": allnode,
            'msg': '获得节点信息'
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 获得机构信息
@app.route('/api/task/getallagency', methods=['POST'])
@auth.permission("task")
def getallagency():
    try:
        g.cursor.execute("""select agency_id,
                               agency_name
                            from (
                            select distinct a.* from sys_agency_info a,h_information_system b
                            where a.agency_id = b.agency_id
                            and b.state = '1' and a.state = '1'
                            ) as a
                            order by agency_id """)
        agencymsg = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": agencymsg,
            'msg': '获得机构信息'
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 获得系统节点级联
@app.route('/api/task/configBaseItems', methods=['POST'])
@auth.permission("task")
def configBaseItems():
    try:
        form = ValidateForm(agency_id=['机构编号', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)

        # 指定目标
        g.cursor.execute("""
            select  label,
                value,
                children
            from (
            select  a.info_sys_name as label,
                a.info_sys_id || ',' || a.info_sys_name as value,
                a.agency_id,
            array_to_json(array_agg(row_to_json(a))) as children   
            from (
            select distinct a.info_sys_id,
                a.agency_id,
                a.info_sys_name ,
                b.sys_node_name  as  label,
                b.sys_node_id || ',' || b.sys_node_name as  value
            from h_information_system as a
            inner join h_system_node as b
            on a.info_sys_id = b.info_sys_id
            and a.state = '1' 
            and b.state = '1'
            and b.sys_node_name <> ''
            and b.sys_node_id <> ''	
            ) as a
            left join sys_agency_info as b
            on a.agency_id = b.agency_id 
            and b.state = '1' 
            group by a.info_sys_name,
                a.info_sys_id,
                a.agency_id
            ) as a
            where a.agency_id = '%s' 
            and  a.label <> '' 
            and  a.value <> '' """ % (data['agency_id']))
        aims = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": aims,
            'msg': '数据插入成功'
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 获得任务列表
@app.route('/api/task/tasklist', methods=['POST'])
@auth.permission("task")
def tasklist():
    try:
        tb = table()
        g.cursor.execute("""select count(1) as total
                            from  (select distinct  a.task_id,
                                        task_name,
                                        case task_state when '1' then '已下发' 
                                                        when '2' then '已完成'
                                                        when '3' then '超时未反馈' end  as task_state ,
                                        case task_type when '1' then '深度分析任务'
                                                        when '2' then '流量监测任务'
                                                        when  '3' then '审计监测任务'
                                                        when '4' then '脆弱性监测任务' end as task_type,
                                        case  when  element_id is NULL then '任务管理' 
                                                else '安全分析' end as task_source,  
                                        d.aim_names,
                                        c.person_name
                                    from h_survey_task_info as a 
                                    inner join h_survey_task_aim as b
                                    on a.task_id = b.task_id 
                                    inner join sys_person  as c 
                                    on c.person_id = a.r_person
                                    inner join (select array_to_string(array_agg(distinct a.agency_name),'、') as aim_names ,
                                            b.task_id
                                        from (
                                        select a.agency_id ,
                                            a.agency_name ,
                                            b.info_sys_name ,
                                            b.info_sys_id,
                                            c.sys_node_id ,
                                            c.sys_node_name
                                        from sys_agency_info as a
                                        left join h_information_system as b 
                                        on a.agency_id = b.agency_id
                                        left join h_system_node as c 
                                        on b.info_sys_id = c.info_sys_id
                                        and  a.state = '1' 
                                        and  b.state = '1' 
                                        and  c.state = '1' ) as a 
                                        inner join h_survey_task_aim as b 
                                        on ((a.agency_id = b.aim_id and a.agency_name = b.aim_name) 
                                            or ( a.info_sys_id = b.aim_id and a.info_sys_name = b.aim_name ) 
                                            or (a.sys_node_id = b.aim_id and a.sys_node_name = b.aim_name)) 
                                        group by b.task_id) as d
                                    on a.task_id = d.task_id) as b  %s  """ % (tb.where(['b.task_name'])))
        total = g.cursor.fetchone()
        
        g.cursor.execute("""select * from (
                                select distinct  a.task_id,
                                        task_name,
                                        case task_state when '1' then '已下发' 
                                                        when '2' then '已完成'
                                                        when '3' then '超时未反馈' end  as task_state ,
                                        case task_type when '1' then '深度分析任务'
                                                        when '2' then '流量监测任务'
                                                        when  '3' then '审计监测任务'
                                                        when '4' then '脆弱性监测任务' end as task_type,
                                        d2t(a.r_time) as r_time,
                                        case  when  element_id is NULL then '任务管理' 
                                                else '安全分析' end as task_source,  
                                        d.aim_names,
                                        c.person_name,
                                        e.feedback,
                                        e.flag
                                    from h_survey_task_info as a 
                                    inner join h_survey_task_aim as b
                                    on a.task_id = b.task_id 
                                    inner join sys_person  as c 
                                    on c.person_id = a.r_person
                                    inner join (select array_to_string(array_agg(distinct a.agency_name),'、') as aim_names ,
                                            b.task_id
                                        from (
                                        select a.agency_id ,
                                            a.agency_name ,
                                            b.info_sys_name ,
                                            b.info_sys_id,
                                            c.sys_node_id ,
                                            c.sys_node_name
                                        from sys_agency_info as a
                                        left join h_information_system as b 
                                        on a.agency_id = b.agency_id
                                        left join h_system_node as c 
                                        on b.info_sys_id = c.info_sys_id
                                        and  a.state = '1' 
                                        and  b.state = '1' 
                                        and  c.state = '1' ) as a 
                                        inner join h_survey_task_aim as b 
                                        on ((a.agency_id = b.aim_id and a.agency_name = b.aim_name) 
                                            or ( a.info_sys_id = b.aim_id and a.info_sys_name = b.aim_name ) 
                                            or (a.sys_node_id = b.aim_id and a.sys_node_name = b.aim_name)) 
                                        group by b.task_id) as d
                                    on a.task_id = d.task_id
                                    left join (
                                        select task_id,
                                            sum(case when feedback_state = '1' then 1 else 0 end)||'/'||count(feedback_state) as feedback,
                                            sum(case when feedback_state = '1' then 1 else 0 end) as flag
                                        from(select distinct  b.task_id, 
                                            agency_id,
                                            feedback_state  
                                        from (
                                            select a.agency_id ,
                                                a.agency_name ,
                                                b.info_sys_name ,
                                                b.info_sys_id,
                                                c.sys_node_id ,
                                                c.sys_node_name
                                            from sys_agency_info as a
                                            left join h_information_system as b 
                                            on a.agency_id = b.agency_id
                                            left join h_system_node as c 
                                            on b.info_sys_id = c.info_sys_id 
                                            and  a.state = '1' 
                                            and  b.state = '1' 
                                            and  c.state = '1' ) as a
                                        inner join h_survey_task_aim as b 
                                        on ((a.agency_id = b.aim_id and a.agency_name = b.aim_name) 
                                            or ( a.info_sys_id = b.aim_id and a.info_sys_name = b.aim_name ) 
                                            or (a.sys_node_id = b.aim_id and a.sys_node_name = b.aim_name)) 
                                        order by b.task_id) as a
                                        group by task_id
                                    ) as e 
                                    on d.task_id = e.task_id ) as b
                            %s %s %s %s """ % (tb.where(['b.task_name']), tb.orderBy(), tb.offset(), tb.limit()))
        return json.dumps({
            "status": "success",
            "data": {
                "dataSource": g.cursor.fetchall(),
                "total": total
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 获得任务列表详情
@app.route('/api/task/taskdetails', methods=['POST'])
@auth.permission("task")
def taskdetails():
    try:
        form = ValidateForm(task_id=['任务编号', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute("""select 
                                distinct a.task_id,
                                d2t(a.issue_time) as issue_time ,
                                a.task_name,
                                a.task_state,
                                a.task_type,
                                a.time_range,
                                a.task_source,
                                a.feedback_time,
                                a.person_name,
                                c.aim_names,
                                b.task_element :: character varying
                            from (
                                select distinct a.task_id,
                                        task_name,
                                        b.issue_time,
                                        case task_state when '1' then '已下发' 
                                                when '2' then '已完成'
                                                when '3' then '超时未反馈' end  as task_state ,
                                        case task_type when '1' then '深度分析任务'
                                                when '2' then '流量监测任务'
                                                when  '3' then '审计监测任务'
                                                when '4' then '脆弱性监测任务' end as task_type,
                                        start_time || ' - ' ||request_return_time as time_range,
                                        case  when  element_id is NULL then '任务管理' 
                                            else '安全分析' end as task_source,
                                        case task_state when '2' then  d2t(feedback_time) 
                                                when '1' then  '等待反馈数据' 
                                            else '超时未反馈数据' end as feedback_time ,
                                        c.person_name    
                                    from h_survey_task_info as a
                                    inner join  h_survey_task_aim as b 
                                    on a.task_id = b.task_id 
                                    inner join sys_person  as c 
                                    on c.person_id = a.r_person  ) as a 
                            inner join (			 
                                    select array_to_string(array_agg(distinct a.agency_name),'、 ') as aim_names ,
                                        b.task_id
                                    from (
                                    select a.agency_id ,
                                        a.agency_name ,
                                        b.info_sys_name ,
                                        b.info_sys_id,
                                        c.sys_node_id ,
                                        c.sys_node_name
                                    from sys_agency_info as a
                                    left join h_information_system as b 
                                    on a.agency_id = b.agency_id
                                    left join h_system_node as c 
                                    on b.info_sys_id = c.info_sys_id
                                    and  a.state = '1' 
                                    and  b.state = '1' 
                                    and  c.state = '1' ) as a 
                                    inner join h_survey_task_aim as b 
                                    on ((a.agency_id = b.aim_id and a.agency_name = b.aim_name) 
                                        or ( a.info_sys_id = b.aim_id and a.info_sys_name = b.aim_name ) 
                                        or (a.sys_node_id = b.aim_id and a.sys_node_name = b.aim_name)) 
                                    group by b.task_id
                                    ) as c
                            on a.task_id = c.task_id  
                            left join  (select array_to_json(array_agg(row_to_json(a))) as task_element,
                                                a.task_id
                                                from (
                                                    select array_to_string(array_agg(element_name),'、 ') as element_name,
                                                        task_id,
                                                        element_type
                                                    from (
                                                    select task_id ,
                                                        case element_type when 'safelevel' then '安全级别' 
                                                                when 'URL' then 'url'
                                                                when 'domain' then 'domain'
                                                                when 'comprotocals' then '通信协议'
                                                                when 'threatname' then '威胁名称' 
                                                                when 'node' then '节点名称'
                                                                when 'MD5' then 'MD5' 
                                                                when 'vulnername' then '漏洞名称' end element_type,
                                                        case element_type when 'safelevel' then '大于'|| element_name || '级' 
                                                            else  element_name end as element_name  
                                                        from h_survey_task_element
                                                    ) as n
                                                    group by task_id,
                                                        element_type
                                                ) as a
                                            group by task_id
                                    ) as b
                            on a.task_id = b.task_id  
                            where a.task_id = %s
                            """ % (data['task_id']))
        task_details = g.cursor.fetchone(),

        g.cursor.execute(""" with tb as (
                                    select split_part(aim_url, '/', 1) as agency_id, 
                                           split_part(aim_url, '/', 2) as info_sys_id, 
                                           split_part(aim_url, '/', 3) as sys_node_id
                                        from h_survey_task_aim
                                    where task_id = '%s'
                                )

                                select  agency_name, 
                                        json_agg(concat_ws('/',info_sys_name, sys_node_name)) as aim
                                from 
                                (select b.agency_name, 
                                        c.info_sys_name, 
                                        d.sys_node_name 
                                    from sys_agency_info b
                                    inner join h_information_system c on b.agency_id = c.agency_id and c.state = '1'
                                    inner join h_system_node d on c.info_sys_id = d.info_sys_id and d.state = '1'
                                    inner join (select * from tb where tb.sys_node_id is not null) a on b.agency_id = a.agency_id and c.info_sys_id = a.info_sys_id and d.sys_node_id = a.sys_node_id
                                union
                                select b.agency_name, 
                                       c.info_sys_name, 
                                       d.sys_node_name 
				    from sys_agency_info b
				    inner join h_information_system c on b.agency_id = c.agency_id and b.state = '1'
				    inner join h_system_node d on c.info_sys_id = d.info_sys_id and d.state = '1'
				    inner join (select * from tb where tb.sys_node_id = '') a on b.agency_id = a.agency_id and c.info_sys_id = a.info_sys_id) f

                                group by f.agency_name
                                                """% (data['task_id']))
        task_config = g.cursor.fetchall(),
        return json.dumps({
            "status": "success",
            "data": {
                "task_details": task_details,
                "task_config": task_config
            }
        })
    except Exception ,e :
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})

# 判断任务名称不能为空
@app.route('/api/task/istaskName', methods=['POST'])
def  istaskName():
    try:
        g.cursor.execute("""select array_agg(task_name) task_name 
                            from h_survey_task_info
                            """ )
        alltaskName = g.cursor.fetchone()
        return json.dumps({
            "status": "success",
            "data": alltaskName,
            "msg": "数据查询成功"
        })
    except Exception ,e :
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 判断任务名称不能为空
@app.route('/api/task/flowMontrior', methods=['POST'])
def  flowMontrior():
    try:
        form = ValidateForm( task_id = ['任务编号', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute("""select file_name,
                                   up_unit,
                                   up_time,
                                   ftp_dowload_addr
                            from h_task_flowcap_result
                            where task_id = '%s'
                            """ % (data['task_id']) )
        alltaskName = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": alltaskName,
            "msg": "数据查询成功"
        })
    except Exception ,e :
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})

        